﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class FieldDAL : IFieldDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        
        public FieldDAL()
        {

        }
        public DataSet LoadFields(FieldInfo fieldInfo)
        {
            string strSQL;
            int ptSeq = 0;
            int etSeq = 0;

            if (fieldInfo.PositionTypes != null)
                ptSeq = fieldInfo.PositionTypes.Sequence;
            if (fieldInfo.EmployeeTypes != null)
                etSeq = fieldInfo.EmployeeTypes.Sequence;
            
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@Sequence", fieldInfo.Sequence);
                dbManager.AddParameters(1, "@PositionTypes", ptSeq);
                dbManager.AddParameters(2, "@EmployeeTypes", etSeq);
                dbManager.AddParameters(3, "@SearchText", string.IsNullOrEmpty(fieldInfo.SearchText) ? "" : fieldInfo.SearchText);
                dbManager.AddParameters(4, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(5, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT f.FIELD_POS_SEQ, f.FIELD_POS_NAME, f.FIELD_POS_NAME_ENG"
                        + ", f.FIELD_POS_LINE, f.FIELD_POS_LINE_ENG, f.REMARKS"
                        + ", f.EMP_TYPE_SEQ, f.POS_TYPE_SEQ, f.FIELD_GROUP_SEQ, f.CLASS_SPEC_SEQ"
                        + ", f.CREATED_BY, f.UPDATED_BY, f.RECORD_STATUS"
                        + ", pt.POS_TYPE_NAME, et.EMP_TYPE_NAME, fg.FIELD_GROUP_NAME, s.CLASS_SPEC_NAME"
                    + " FROM HRM_MP_FIELD_POSITIONS f"
                    + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON f.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                    + " LEFT JOIN HRM_MP_FIELD_GROUPS fg ON f.FIELD_GROUP_SEQ = fg.FIELD_GROUP_SEQ"
                    + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS s ON f.CLASS_SPEC_SEQ = s.CLASS_SPEC_SEQ"
                    + " WHERE 0 = 0" //"
                    + (fieldInfo.Sequence > 0 ? " AND f.FIELD_POS_SEQ = @Sequence"
                        : " AND f.RECORD_STATUS = @ActiveRecord"
                            + (ptSeq > 0 ? " AND f.POS_TYPE_SEQ = @PositionTypes" : "")
                            + (etSeq > 0 ? " AND f.EMP_TYPE_SEQ = @EmployeeTypes" : "")
                       );
                   
                if( fieldInfo.SearchText!=null &&   ! (fieldInfo.SearchText.Trim()=="") ) 
                {
                        strSQL+= (" AND (f.FIELD_POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                            + " OR pt.POS_TYPE_NAME LIKE CONCAT('%', @SearchText , '%')"
                            + " OR s.CLASS_SPEC_NAME LIKE CONCAT('%', @SearchText , '%')"
                            + ") ");
                }
                strSQL += " ORDER BY f.POS_TYPE_SEQ DESC, f.FIELD_GROUP_SEQ, f.FIELD_POS_CODE";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public DataSet LoadFieldExists(FieldInfo fieldInfo)
        {
            string strSQL;
            int ptSeq = 0;
            int etSeq = 0;

            if (fieldInfo.PositionTypes != null)
                ptSeq = fieldInfo.PositionTypes.Sequence;
            if (fieldInfo.EmployeeTypes != null)
                etSeq = fieldInfo.EmployeeTypes.Sequence;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@Sequence", fieldInfo.Sequence);
                dbManager.AddParameters(1, "@PositionTypes", ptSeq);
                dbManager.AddParameters(2, "@EmployeeTypes", etSeq);
                dbManager.AddParameters(3, "@SearchText", string.IsNullOrEmpty(fieldInfo.SearchText) ? "" : fieldInfo.SearchText);
                dbManager.AddParameters(4, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(5, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT f.FIELD_POS_SEQ, f.FIELD_POS_NAME, f.FIELD_POS_NAME_ENG"
                        + ", f.FIELD_POS_LINE, f.FIELD_POS_LINE_ENG, f.REMARKS"
                        + ", f.EMP_TYPE_SEQ, f.POS_TYPE_SEQ, f.FIELD_GROUP_SEQ, f.CLASS_SPEC_SEQ"
                        + ", f.CREATED_BY, f.UPDATED_BY, f.RECORD_STATUS"
                        + ", pt.POS_TYPE_NAME, et.EMP_TYPE_NAME, fg.FIELD_GROUP_NAME, s.CLASS_SPEC_NAME"
                    + " FROM HRM_MP_FIELD_POSITIONS f"
                    + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON f.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                    + " LEFT JOIN HRM_MP_FIELD_GROUPS fg ON f.FIELD_GROUP_SEQ = fg.FIELD_GROUP_SEQ"
                    + " LEFT JOIN HRM_MP_CLASS_SPECIFICATIONS s ON f.CLASS_SPEC_SEQ = s.CLASS_SPEC_SEQ"
                    + " WHERE 0 = 0"; //"
                if (fieldInfo.SearchText != null && !(fieldInfo.SearchText.Trim() == ""))
                {
                    strSQL += (" AND (f.FIELD_POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                    + " OR pt.POS_TYPE_NAME LIKE CONCAT('%', @SearchText , '%')"
                    + " OR s.CLASS_SPEC_NAME LIKE CONCAT('%', @SearchText , '%')"
                    + ") ");
                }

                    strSQL+= (fieldInfo.Sequence > 0 ? " AND f.FIELD_POS_SEQ = @Sequence"
                        : " AND f.RECORD_STATUS = @ActiveRecord"
                            + (ptSeq > 0 ? " AND f.POS_TYPE_SEQ = @PositionTypes" : "")
                            + (etSeq > 0 ? " AND f.EMP_TYPE_SEQ = @EmployeeTypes" : ""))
                    + " AND f.FIELD_POS_SEQ IN("
                                + " select field_pos_seq from hrm_mp_positions"
                                + " where record_status = @ActiveRecord"
                                + " and emp_type_seq = @EmployeeTypes"
                                + ")"
                    + " ORDER BY f.POS_TYPE_SEQ DESC, f.FIELD_GROUP_SEQ, f.FIELD_POS_CODE";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }    
        public IList<FieldInfo> ListFields(FieldInfo fieldInfo)
        {
            string strSQL;
            int ptSeq = 0;
            int etSeq = 0;

            if (fieldInfo.PositionTypes != null)
                ptSeq = fieldInfo.PositionTypes.Sequence;
            if (fieldInfo.EmployeeTypes != null)
                etSeq = fieldInfo.EmployeeTypes.Sequence;

            List<FieldInfo> infoList = new List<FieldInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", fieldInfo.Sequence);
                dbManager.AddParameters(1, "@PositionTypes", ptSeq);
                dbManager.AddParameters(2, "@EmployeeTypes", etSeq);
                dbManager.AddParameters(3, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(4, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT f.FIELD_POS_SEQ, f.FIELD_POS_NAME, f.FIELD_POS_NAME_ENG"
                        + ", f.FIELD_POS_LINE, f.FIELD_POS_LINE_ENG, f.REMARKS"
                        + ", f.EMP_TYPE_SEQ, f.POS_TYPE_SEQ, f.FIELD_GROUP_SEQ, f.CLASS_SPEC_SEQ"
                        + ", f.CREATED_BY, f.UPDATED_BY, f.RECORD_STATUS"
                    + " FROM HRM_MP_FIELD_POSITIONS f"
                    + " WHERE 0 = 0" //"
                    + (fieldInfo.Sequence > 0 ? " AND f.FIELD_POS_SEQ = @Sequence"
                        : " AND f.RECORD_STATUS = @ActiveRecord"
                            + (ptSeq > 0 ? " AND f.POS_TYPE_SEQ = @PositionTypes" : "")
                            + (etSeq > 0 ? " AND f.EMP_TYPE_SEQ = @EmployeeTypes" : "")
                       )
                    + " ORDER BY f.POS_TYPE_SEQ DESC, f.FIELD_GROUP_SEQ, f.FIELD_POS_CODE";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeTypeDAL empDAL = new EmployeeTypeDAL();
                    EmployeeTypeInfo empInfo = new EmployeeTypeInfo();
                    IList<EmployeeTypeInfo> empList = empDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.IsDBNull(6) ? 0 : rdr.GetInt32(6)));
                    if (empList.Count > 0)
                        empInfo = empList[0];

                    PositionTypeDAL posDAL = new PositionTypeDAL();
                    PositionTypeInfo postypeInfo = new PositionTypeInfo();
                    if (!rdr.IsDBNull(7))
                    {
                        IList<PositionTypeInfo> posList = posDAL.ListPositionTypes(rdr.IsDBNull(7) ? (int?)null : rdr.GetInt32(7), null);
                        if (posList.Count > 0)
                            postypeInfo = posList[0];
                    }

                    FieldGroupDAL groupDAL = new FieldGroupDAL();
                    FieldGroupInfo groupInfo = new FieldGroupInfo();
                    IList<FieldGroupInfo> groupList = groupDAL.ListFieldGroups(rdr.IsDBNull(8) ? 0 : rdr.GetInt32(8), null);
                    if (groupList.Count > 0)
                    {
                        groupInfo = groupList[0];
                        //groupInfo.Description = groupList[0].Name + groupList[0].Description;
                    }

                    ClassSpecificationDAL specDAL = new ClassSpecificationDAL();
                    ClassSpecificationInfo specInfo = new ClassSpecificationInfo();
                    IList<ClassSpecificationInfo> specList = specDAL.ListClassSpecification(rdr.IsDBNull(9) ? 0 : rdr.GetInt32(9), null);
                    if (specList.Count > 0)
                        specInfo = specList[0];

                    FieldInfo Info = new FieldInfo(rdr.GetInt32(0)
                            , rdr.IsDBNull(1) ? "" : rdr.GetString(1)
                            , rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                            , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                            , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                            , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                            , empInfo
                            , postypeInfo
                            , groupInfo
                            , specInfo
                            , rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10)
                            , rdr.IsDBNull(11) ? (int?)null : rdr.GetInt32(11));
                    infoList.Add(Info);
                }

                return infoList;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }        
        public void Insert(FieldInfo fieldInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@FieldPosName", fieldInfo.Name);
                dbManager.AddParameters(1, "@FieldPosNameEng", fieldInfo.NameEng);
                dbManager.AddParameters(2, "@FieldPosLine", fieldInfo.LineName);
                dbManager.AddParameters(3, "@FieldPosLineEng", fieldInfo.LineNameEng);
                dbManager.AddParameters(4, "@EmpTypeSeq", fieldInfo.EmployeeTypes.Sequence);
                dbManager.AddParameters(5, "@PosTypeSeq", fieldInfo.PositionTypes.Sequence);
                dbManager.AddParameters(6, "@FieldGroupSeq", fieldInfo.FieldGroups.Sequence);
                dbManager.AddParameters(7, "@ClassSpecSeq", fieldInfo.ClassSpecifications.Sequence);
                dbManager.AddParameters(8, "@Remarks", fieldInfo.Remarks);
                dbManager.AddParameters(9, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@CreatedBy", fieldInfo.CreatedBy);
                dbManager.AddParameters(11, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_MP_FIELD_POSITIONS(FIELD_POS_NAME, FIELD_POS_NAME_ENG"
                        + ", FIELD_POS_LINE, FIELD_POS_LINE_ENG, EMP_TYPE_SEQ, POS_TYPE_SEQ"
                        + ", FIELD_GROUP_SEQ, CLASS_SPEC_SEQ, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@FieldPosName, @FieldPosNameEng, @FieldPosLine, @FieldPosLineEng"
                        + ", @EmpTypeSeq, @PosTypeSeq, @FieldGroupSeq, @ClassSpecSeq, @Remarks"
                        + ", @RecordStatus, @CreatedBy, @CreatedWhen)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update(FieldInfo fieldInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@Seq", fieldInfo.Sequence);
                dbManager.AddParameters(1, "@FieldPosName", fieldInfo.Name);
                dbManager.AddParameters(2, "@FieldPosNameEng", fieldInfo.NameEng);
                dbManager.AddParameters(3, "@FieldPosLine", fieldInfo.LineName);
                dbManager.AddParameters(4, "@FieldPosLineEng", fieldInfo.LineNameEng);
                dbManager.AddParameters(5, "@EmpTypeSeq", fieldInfo.EmployeeTypes.Sequence);
                dbManager.AddParameters(6, "@PosTypeSeq", fieldInfo.PositionTypes.Sequence);
                dbManager.AddParameters(7, "@FieldGroupSeq", fieldInfo.FieldGroups.Sequence);
                dbManager.AddParameters(8, "@ClassSpecSeq", fieldInfo.ClassSpecifications.Sequence);
                dbManager.AddParameters(9, "@Remarks", fieldInfo.Remarks);
                dbManager.AddParameters(10, "@UpdatedBy", fieldInfo.UpdatedBy);
                dbManager.AddParameters(11, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_FIELD_POSITIONS"
                           + " SET FIELD_POS_NAME = @FieldPosName"
                                + ", FIELD_POS_NAME_ENG = @FieldPosNameEng"
                                + ", FIELD_POS_LINE = @FieldPosLine"
                                + ", FIELD_POS_LINE_ENG = @FieldPosLineEng"
                                + ", EMP_TYPE_SEQ = @EmpTypeSeq"
                                + ", POS_TYPE_SEQ = @PosTypeSeq"
                                + ", FIELD_GROUP_SEQ = @FieldGroupSeq"
                                + ", CLASS_SPEC_SEQ = @ClassSpecSeq"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE FIELD_POS_SEQ = @Seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_MP_FIELD_POSITIONS"
                    + " SET RECORD_STATUS = @record_status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE FIELD_POS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        
    }
}
